﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;

namespace XmlPlayers
{
    class Program
    {
        static void Main(string[] args)
        {

            //CreateXMLPlayers();
            ReadXMLPlayers();

        }

        public static void ReadXMLPlayers()
        {
            XmlDocument doc = new XmlDocument();
            doc.Load("games.xml");
            bool more = true;
            while (more == true)
            {
                more = false;
                foreach (XmlNode node in doc.DocumentElement)
                {
                    foreach (XmlNode teamNode in node.ChildNodes)
                    {
                        FootballDataContext fb = new FootballDataContext();
                        var playerTeamId =
                               (from t in fb.Teams
                                join
                                  m in fb.Matches on t.TeamId equals m.HomeId
                                where m.MatchId == int.Parse(node.Attributes[0].Value)
                                select t).First();
                        if (teamNode.Name == "away-team")
                        {
                            playerTeamId =
                               (from t in fb.Teams
                                join
                                  m in fb.Matches on t.TeamId equals m.AwayId
                                where m.MatchId == int.Parse(node.Attributes[0].Value)
                                select t).First();
                        }
                        foreach (XmlNode player in teamNode.ChildNodes)
                        {
                            foreach (XmlNode playerName in player.Attributes)
                            {
                                if (playerName.Name == "name")
                                {

                                    Console.WriteLine(
                                    playerName.Value + " " + playerTeamId.TeamId + " " + node.Attributes[0].Value);

                                    var playerId =
                                        (from p in fb.Players
                                         where p.Name == playerName.Value
                                         select p.PlayerId).First();
                                    Console.WriteLine(playerId);
                                }
                            }
                        }
                    }
                }
            }
        }

        public static void CreateXMLPlayers()
        {
            XmlDocument doc = new XmlDocument();
            XmlDeclaration declaration = doc.CreateXmlDeclaration("1.0", "UTF-8", "");
            doc.AppendChild(declaration);
            XmlElement games = doc.CreateElement("games");
            doc.AppendChild(games);
            FootballDataContext fb = new FootballDataContext();
            var allGames =
                from m in fb.Matches
                select m;
            foreach (var match in allGames)
            {
                XmlElement game = doc.CreateElement("game");
                games.AppendChild(game);
                XmlAttribute gameId = doc.CreateAttribute("id");
                gameId.Value = match.MatchId.ToString();
                game.SetAttributeNode(gameId);

                var homeTeamPlayers =
                    from t in fb.Teams
                    join
                    m in fb.Matches on t.TeamId equals match.HomeId
                    join
                    mp in fb.PlayersMatches on m.MatchId equals mp.MatchId
                    join
                    p in fb.Players on mp.PlayerId equals p.PlayerId
                    where match.MatchId == m.MatchId && p.TeamId == t.TeamId
                    select new { PlayerName = p.Name, PlayerNumber = p.Number };
                XmlElement homePlayers = doc.CreateElement("home-team");
                game.AppendChild(homePlayers);
                foreach (var homePlayer in homeTeamPlayers)
                {
                    XmlElement player = doc.CreateElement("player");
                    homePlayers.AppendChild(player);
                    XmlAttribute playerNumber = doc.CreateAttribute("number");
                    playerNumber.Value = homePlayer.PlayerNumber.ToString();
                    player.SetAttributeNode(playerNumber);
                    XmlAttribute playerName = doc.CreateAttribute("name");
                    playerName.Value = homePlayer.PlayerName;
                    player.SetAttributeNode(playerName);
                }


                var awayTeamPlayers =
    from t in fb.Teams
    join
    m in fb.Matches on t.TeamId equals match.AwayId
    join
    mp in fb.PlayersMatches on m.MatchId equals mp.MatchId
    join
    p in fb.Players on mp.PlayerId equals p.PlayerId
    where match.MatchId == m.MatchId && p.TeamId == t.TeamId
    select new { PlayerName = p.Name, PlayerNumber = p.Number };
                XmlElement awayPlayers = doc.CreateElement("away-team");
                game.AppendChild(awayPlayers);
                foreach (var awayPlayer in awayTeamPlayers)
                {
                    XmlElement player = doc.CreateElement("player");
                    awayPlayers.AppendChild(player);
                    XmlAttribute playerNumber = doc.CreateAttribute("number");
                    playerNumber.Value = awayPlayer.PlayerNumber.ToString();
                    player.SetAttributeNode(playerNumber);
                    XmlAttribute playerName = doc.CreateAttribute("name");
                    playerName.Value = awayPlayer.PlayerName;
                    player.SetAttributeNode(playerName);
                }


            }
            doc.Save("games.xml");
        }
    }
}
